package jee.boot.module.iexp.excel.template;


import jee.boot.common.basic.ServiceException;
import jee.boot.common.utils.DateUtils;
import jee.boot.common.utils.Encodes;
import jee.boot.common.utils.StringUtils;
import jee.boot.common.utils.excel.excel.Function;
import jee.boot.common.utils.excel.service.DictionaryTranslateHelper;
import jee.boot.common.utils.excel.service.EntityTranslateHelper;
import jee.boot.entity.task.*;
import jee.boot.module.iexp.excel.excel.ExcelExport;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

/**
 * 通过excel模板导出excel文件工具类
 *
 * @author zhilei.wang
 */
public class ExportTemplateExcelExecutor {
    private Logger logger= LoggerFactory.getLogger(getClass());
    private byte[] b;//输出流byte
    private InputStream is;//输入模板流
    private Map<String, Object> data = new HashMap<>();//需要导出的数据
    private List<DynamicSheet> dynamicSheets;//多动态sheet导出
    private boolean exportDynamicSheet = false;//是否启用动态sheet页导出,动态sheet导出会增加性能消耗,非必要默认不开启
    private boolean resetCellFormula = false;//重置单元格公式,这个会增加性能消耗,非必要默认不开启

    /**
     * 通过模板名称+导出数据进行初始化配置
     *
     * @param templateName 模板名称
     * @param data         导出数据
     * @return
     * @throws IOException
     */
    public ExportTemplateExcelExecutor initByTemplateNameAndData(String templateName, Map<String, Object> data) throws IOException {
        String pathName = "/templates/excel/" + templateName;
        Resource resource = new ClassPathResource(pathName);
        this.is = resource.getInputStream();
        this.data.putAll(data);
        return this;
    }

    /**
     * 通过模板名称+导出数据进行初始化配置
     *
     * @param templatePath 模板物理路径
     * @param data         导出数据
     * @return
     * @throws IOException
     */
    public ExportTemplateExcelExecutor initByTemplatePathAndData(String templatePath, Map<String, Object> data) throws IOException {
        this.is = new FileInputStream(templatePath);
        this.data.putAll(data);
        return this;
    }

    /**
     * 通过模型+导出数据进行初始化配置,根据提供的模型生成动态的模板文件
     *
     * @param model 导出数据的模型
     * @param data  导出数据
     * @return
     * @throws IOException
     */
    public ExportTemplateExcelExecutor initByModelAndData(IExportModel model, Map<String, Object> data) throws IOException {
        this.is = createTemplateByModel(model);
        this.data.putAll(data);
        return this;
    }

    /**
     * 只通过简单的数据列表进行导出(默认的列为list的第一个对象的所有属性)
     *
     * @param list
     * @return
     * @throws IOException
     */
    public ExportTemplateExcelExecutor initByListData(List<Map<String, Object>> list) throws IOException {
        if (list != null && !list.isEmpty()) {
            IExportModel model = autoModelByMap(list.get(0));
            Map<String, Object> data = new HashMap<String, Object>();
            data.put("list", list);
            return this.initByModelAndData(model, data);
        }
        throw new ServiceException(1422);
    }

    /**
     * 配置动态sheet导出
     *
     * @param dynamicSheets
     * @return
     */
    public ExportTemplateExcelExecutor configDynamicSheet(List<DynamicSheet> dynamicSheets) {
        if (dynamicSheets != null && !dynamicSheets.isEmpty()) {
            this.dynamicSheets = dynamicSheets;
            this.exportDynamicSheet = true;
        }
        return this;
    }

    /**
     * 配置是否需要重置导出的excel的公式,默认是false,因为导出的数据如果excel中有公式,导出的时候显示不出来,双击下才会显示结果
     *
     * @param resetCellFormula
     * @return
     */
    public ExportTemplateExcelExecutor configResetCellFormula(boolean resetCellFormula) {
        this.resetCellFormula = resetCellFormula;
        return this;
    }

    /**
     * 将map转换成model
     *
     * @param objectMap
     * @return
     */
    private IExportModel autoModelByMap(Map<String, Object> objectMap) {
        List<ExportFieldImpl> fieldList = new ArrayList<>();
        ExportModelImpl model = new ExportModelImpl();
        model.setExportFieldList(fieldList);
        for (Map.Entry<String, Object> entity : objectMap.entrySet()) {
            String key = entity.getKey();
            ExportFieldImpl def = new ExportFieldImpl();
            def.setFieldName(key);
            def.setTitle(key);
            fieldList.add(def);
        }
        return model;
    }

    /**
     * 将提供的模型转换为动态的导出模板文件流
     *
     * @param model 模型
     * @return 文件流
     */
    private InputStream createTemplateByModel(IExportModel model) {
        List<? extends IExportField> exportFieldList = model.getExportFieldList();
        List<String> headList = new ArrayList<>();
        for (IExportField f : exportFieldList) {
            headList.add(f.getTitle());
        }
        String title = model.getTitle();
        ExcelExport tempExcel = new ExcelExport(model.getTitle(), headList, null);
        if (exportFieldList != null && !exportFieldList.isEmpty()) {
            Row forBegin = tempExcel.addRow();
            tempExcel.addCell(forBegin, 0, "<jx:forEach  items=\"${list}\" var=\"entity\">");
            Row data = tempExcel.addRow();
            int cell = 0;
            for (IExportField ef : exportFieldList) {
                // 按名称把format缓存起来,poi对特殊字符的字符串常量解析有问题,所以使用变量吧
                if (StringUtils.isNotBlank(ef.getFormat())) {
                    this.data.put(ef.getFieldName() + "_format", ef.getFormat());
                }
                String val = "";
                //如果是字典类型:${function.dic("del_flag", data.dictField)}
                if (StringUtils.isNotBlank(ef.getDictType())) {
                    if (StringUtils.isBlank(ef.getDictField())) {
                        val = "${function.dics(\"" + ef.getDictType() + "\",entity." + ef.getFieldName() + ")}";
                    } else {
                        val = "${function.dics(\"" + ef.getDictType() + "\",entity." + ef.getFieldName() + ",\"" + ef.getDictField() + "\")}";
                    }
                } else {
                    if (StringUtils.isNotBlank(ef.getFormat())) {
                        // 如果是时间类型的格式化:${function.dateFormat(data.dateField1e, "yyyy-mm-dd")}
                        if (Date.class.equals(ef.getFieldType())) {
                            val = "${function.dateFormat(entity." + ef.getFieldName() + "," + ef.getFieldName() + "_format" + ")}";
                        } else {// 数字类型格式化:${function.format(data.dateField1e, "0.00")}
                            val = "${function.format(entity." + ef.getFieldName() + "," + ef.getFieldName() + "_format" + ")}";
                        }
                    } else {
                        val = "${entity." + ef.getFieldName() + "}";
                    }
                }
                Cell dataCell = tempExcel.addCell(data, cell++, val);
            }
            Row forEnd = tempExcel.addRow();
            tempExcel.addCell(forEnd, 0, "</jx:forEach>");
        }
        try {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            tempExcel.write(out);
            InputStream in = new ByteArrayInputStream(out.toByteArray());
            return in;
        } catch (Exception e) {
            logger.error(e.getMessage(),e);
        }
        return null;
    }

    public ExportTemplateExcelExecutor buildExcel() throws Exception {

        if (exportDynamicSheet) {
            // 动态sheet页excel导出,使用动态构建
            buildDynamicExcel();
        } else {
            // 非动态sheet页exce导出,使用标准构建
            buildTypicalExcel();
        }
        return this;
    }

    /**
     * 构建标准excel文件
     *
     * @return
     * @throws Exception
     */
    private ExportTemplateExcelExecutor buildTypicalExcel() throws Exception {
        ByteArrayOutputStream bos = null;
        if (is == null) {
            throw new ServiceException(1423);
        }
        try {
            DictionaryTranslateHelper helper = DictionaryTranslateHelper.getInstance();
            EntityTranslateHelper entityTranslateHelper = EntityTranslateHelper.getInstance();
            data.put("function", new Function(helper, entityTranslateHelper));
            // 关联模板
            XLSTransformer transformer = new XLSTransformer();
            Workbook workBook = transformer.transformXLS(is, data);
            if (resetCellFormula) {
                resetCellFormula(workBook);
            }
            helper.clear();
            entityTranslateHelper.clear();
            bos = new ByteArrayOutputStream();
            workBook.write(bos);
            b = bos.toByteArray();
            return this;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (null != bos) {
                    bos.close();
                }
            } catch (IOException e) {
                throw e;
            }
        }
    }

    /**
     * 构建动态excel文件
     *
     * @return
     * @throws Exception
     */
    private ExportTemplateExcelExecutor buildDynamicExcel() throws Exception {
        ByteArrayOutputStream bos = null;
        if (is == null) {
            throw new ServiceException(1423);
        }
        try {
            data.put("function", new Function());
            // 关联模板
            XLSTransformer transformer = new XLSTransformer();
            Workbook workBook = templateWorkBook();
            cloneSheets();
            transformer.transformWorkbook(workBook, data);
            if (resetCellFormula) {
                resetCellFormula(workBook);
            }
            bos = new ByteArrayOutputStream();
            workBook.write(bos);
            b = bos.toByteArray();
            return this;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (null != bos) {
                    bos.close();
                }
            } catch (IOException e) {
                throw e;
            }
        }
    }

    /**
     * 读取模板,转换成Workbook(带脚本,未解析)
     *
     * @return
     * @throws FileNotFoundException
     */
    public Workbook templateWorkBook() {
        Workbook xssfWorkbook = null;
        try {
            xssfWorkbook = WorkbookFactory.create(is);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return xssfWorkbook;
    }

    /**
     * 替换表达式主属性名称,主要应用于一个sheet模板页,导出多个sheet页的excel,
     * 实际应用场景: 根据用户详情信息模板,导出所有用户的详情信息到不同的sheet中,sheet页名称为用户名
     *
     * @param originalExpression    需要被替换的表达式 比如: ${user.role.name}
     * @param expressionToReplace   替换之后的名称: 比如: user_1
     * @param expressionReplacement 主属性被替换的名称,比如: user
     * @return 最终返回的结果: ${user_1.role.name}
     */
    public String replaceExpressions(String originalExpression, String expressionToReplace, String expressionReplacement) {
        return originalExpression.replaceAll("(\\$\\{)" + expressionToReplace + "((\\b.*)?\\})", "$1" + expressionReplacement + "$2");
    }

    /**
     * 根据数据中的sheet信息对模板sheet进行复制(基于二进制流进行复制)
     */
    public void cloneSheets() {
        Workbook workbook = templateWorkBook();
        int numberOfSheets = workbook.getNumberOfSheets();
        int oldNumberOfSheets = numberOfSheets;//原始模板中的sheet数量
        for (DynamicSheet sheet : dynamicSheets) {
            int sheetIndex = workbook.getSheetIndex(sheet.getSheetNameInTemplate());
            Sheet newSheet = workbook.cloneSheet(sheetIndex);
            workbook.setSheetName(numberOfSheets, sheet.getSheetNameInData());
            for (int i = newSheet.getFirstRowNum(), c = newSheet.getLastRowNum(); i <= c; i++) {
                org.apache.poi.ss.usermodel.Row srcRow = newSheet.getRow(i);
                if (srcRow != null) {
                    if (srcRow.getFirstCellNum() >= 0 && srcRow.getLastCellNum() >= 0) {
                        for (int j = srcRow.getFirstCellNum(), c1 = srcRow.getLastCellNum(); j <= c1; j++) {
                            org.apache.poi.ss.usermodel.Cell oldCell = srcRow.getCell(j);
                            if (oldCell != null) {
                                if (oldCell.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) {
                                    String oldValue = oldCell.getRichStringCellValue().getString();
                                    String newValue = replaceExpressions(oldValue, sheet.getEntityNameInData(), sheet.getEntityNameInTemplate());
                                    oldCell.setCellValue(workbook.getCreationHelper().createRichTextString(newValue));
                                }
                            }
                        }
                    }
                }
            }
            numberOfSheets++;
        }
        // 删除旧的模板sheet页
        for (int i = oldNumberOfSheets; i > 0; i--) {
            workbook.removeSheetAt(0);
        }
    }

    /**
     * 重置单元格的公式
     *
     * @param workbook
     */
    public static void resetCellFormula(Workbook workbook) {
        for (int k = 0, w = workbook.getNumberOfSheets(); k < w; k++) {
            Sheet newSheet = workbook.getSheetAt(k);
            for (int i = newSheet.getFirstRowNum(), c = newSheet.getLastRowNum(); i <= c; i++) {
                org.apache.poi.ss.usermodel.Row srcRow = newSheet.getRow(i);
                if (srcRow != null) {
                    if (srcRow.getFirstCellNum() >= 0 && srcRow.getLastCellNum() >= 0) {
                        for (int j = srcRow.getFirstCellNum(), c1 = srcRow.getLastCellNum(); j <= c1; j++) {
                            org.apache.poi.ss.usermodel.Cell oldCell = srcRow.getCell(j);
                            if (oldCell != null) {
                                if (oldCell.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) {
                                    String oldValue = oldCell.getRichStringCellValue().getString();
                                    if (StringUtils.startsWith(oldValue, "=")) {
                                        oldCell.setCellFormula(StringUtils.removeStart(oldValue, "="));
                                    }
                                } else if (oldCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                                    String oldValue = oldCell.getCellFormula();
                                    oldCell.setCellFormula(oldValue);

                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 输出数据流
     *
     * @param os 输出数据流
     */
    public ExportTemplateExcelExecutor write(OutputStream os) throws IOException {
        os.write(b);
        return this;
    }

    /**
     * 输出到客户端
     *
     * @param fileName 输出文件名
     */
    public ExportTemplateExcelExecutor write(HttpServletResponse response, String fileName) throws IOException {
        return write(response, fileName, "xlsx");
    }

    public ExportTemplateExcelExecutor write(HttpServletResponse response, String fileName, String extName) throws IOException {
        String finalName = fileName + DateUtils.getDate("_yyyyMMddHHmmss") + "." + extName;
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(finalName));
        write(response.getOutputStream());
        return this;
    }

    /**
     * 输出到文件
     *
     * @param fileName 输出文件名
     */
    public ExportTemplateExcelExecutor writeFile(String fileName) throws FileNotFoundException, IOException {
        FileOutputStream os = new FileOutputStream(fileName);
        this.write(os);
        return this;
    }

    /**
     * 清理临时数据
     */
    public ExportTemplateExcelExecutor dispose() {
        b = null;
        is = null;
        data.clear();
        return this;
    }
}
